from openpyxl import load_workbook
from openpyxl import Workbook
import sys


def int_to_ip(num):
    """
    对十进制数转换成IP地址。
    :param num: 十进制。
    :return: IP地址。
    """
    ip = ''
    for i in (24, 16, 8):
        ip += str(num >> i & 0xff)
        ip += '.'
    ip += str(num & 0xff)
    return ip


class Excel:
    def __init__(self, read_path=None, set_read_sheet=None, write_path=None, set_write_sheet=None, read_column=None):
        lwk = load_workbook(filename=read_path)
        self.read_sheet = lwk[set_read_sheet]
        self.read_mem_sheet = self._load_all_sheet(read_column)
        if write_path is not None or set_write_sheet is not None:
            self.wb = Workbook()
            self.write_work = self.wb.create_sheet(set_write_sheet)
            self.filter_comment()
            self.work_save(write_path)

    def _load_all_sheet(self, read_column):
        """
        从excel文件中加载所有数据到内存。
        :param read_column: 需要读取的列。
        :return: 字典。
        """
        sheet = {}
        for line in range(2, 9999):
            temp = {}
            for column in read_column:
                c = column + str(line)
                temp[c] = self.read_sheet[c].value
            if any(temp.values()):
                sheet.update(temp)
            else:
                break
        return sheet

    def filter_comment(self):
        """
        对a列值计算成IP地址，对c列的值进行过滤。
        :return:
        """
        for key in self.read_mem_sheet.keys():
            comment_value = self.read_mem_sheet[key]
            if 'a' in key and isinstance(comment_value, int):
                self.read_mem_sheet[key] = int_to_ip(comment_value)
            if 'c' in key and isinstance(comment_value, str) and '\n' in comment_value:
                comments = comment_value.split('\n')
                for c in comments:
                    for name in ['root/', 'unadmin/', 'admin/', 'administrator/']:
                        if name in c:
                            self.read_mem_sheet[key] = c
                            break

    def work_save(self, filename):
        for key in self.read_mem_sheet.keys():
            value = self.read_mem_sheet[key]
            self.write_work[key] = value
        self.wb.save(filename=filename)

    def diff(self):
        read_path = 'f:\\nodata\\nodata.txt'
        write_path = 'f:\\nodata\\out_nodata.txt'
        with open(read_path) as read:
            for r_line in read:
                txt = r_line.split(' ')[1]
                if '\n' in txt:
                    txt = txt.replace('\n', '')
                for n in range(2, len(self.read_mem_sheet)):
                    ip = self.read_mem_sheet.get('a' + str(n))
                    if ip:
                        if txt == ip:
                            # print(txt, self.read_mem_sheet['a' + str(n)])
                            pwd = self.read_mem_sheet.get('c' + str(n))
                            user, _pwd = '', ''
                            if '/' in pwd:
                                # user, _pwd = pwd.split('/')
                                try:
                                    user, _pwd = pwd.split('/')
                                except Exception:
                                    print(pwd)
                            else:
                                continue
                            with open(write_path, 'a') as write:
                                try:
                                    write.write('{} ansible_ssh_host={}  ansible_ssh_user={} ansible_ssh_pass={}\n'.format(ip, ip, user, _pwd))
                                except Exception:
                                    print(ip, file=sys.stderr)
                            break
                    else:
                        continue


def main():
    #
    # path = 'f:\\1.xlsx'
    # write_path = 'f:\\2.xlsx'
    # excel = Excel(read_path=path, set_read_sheet='1', read_column='abc', write_path=write_path, set_write_sheet='1')

    read_path = 'f:\\nodata\\ip.xlsx'
    excel = Excel(read_path=read_path, set_read_sheet='ip', read_column='abc')
    excel.diff()


if __name__ == '__main__':
    main()